import pandas as pd
import matplotlib.pyplot as plt
from matplotlib.font_manager import _rebuild

_rebuild()
plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

# 显示所有列
pd.set_option('display.max_columns', None)
# 男成绩
df = pd.read_excel('../homework10/18级高一体测成绩汇总.xls', sheet_name='男')
# 女成绩
df2 = pd.read_excel('../homework10/18级高一体测成绩汇总.xls', sheet_name='女')

df['男1000米跑'] = df['男1000米跑'].map(lambda x: float(str(x).replace('\'', '.')))
df.iloc[:, 2:] = df.iloc[:, 2:].applymap(float)
df2.iloc[:, 2:] = df2.iloc[:, 2:].applymap(float)

df_r1 = pd.cut(df['男1000米跑'], bins=3, labels=['好', '中', '差']).value_counts()
df_r2 = pd.cut(df['男引体'], bins=3, labels=['差', '中', '好']).value_counts()

plt.figure('高一男成绩分布', facecolor='lightgray')
plt.subplot(1, 2, 1)
plt.title('男1000米成绩')
plt.pie(df_r1, labels=df_r1.index)
plt.subplot(1, 2, 2)
plt.title('男引体成绩')
plt.pie(df_r2, labels=df_r2.index)

plt.show()

df2_r1 = pd.qcut(df2['女800米跑'], 4, labels=['优', '良', '中', '差']).value_counts()
df2_r2 = pd.qcut(df2['女跳远'], 4, labels=['差', '中', '良', '优']).value_counts()

plt.figure('高一女成绩分布', facecolor='lightgray')
plt.subplot(1, 2, 1)
plt.title('女800米成绩')
plt.pie(df2_r1, labels=df2_r1.index)
plt.subplot(1, 2, 2)
plt.title('女跳远成绩')
plt.pie(df2_r2, labels=df2_r2.index)

plt.show()


def transform4girl(x):
    if x <= 16.4:
        return '低体重'
    elif x > 16.4 and x <= 22.7:
        return '正常'
    elif x > 22.7 and x <= 25.2:
        return '超重'
    else:
        return '肥胖'


def transform4boy(x):
    if x <= 16.4:
        return '低体重'
    elif x > 16.4 and x <= 23.2:
        return '正常'
    elif x > 23.2 and x <= 26.3:
        return '超重'
    else:
        return '肥胖'


df['BMI'] = ((df['体重'] / df['身高'] / df['身高'] * 10000).round(2)).map(transform4boy)
df2['BMI'] = ((df2['体重'] / df2['身高'] / df2['身高'] * 10000).round(2)).map(transform4girl)

df_r3 = df['BMI'].value_counts()
df2_r3 = df2['BMI'].value_counts()

plt.figure('男女对比')
plt.pie(df_r3, radius=1, pctdistance=0.85, labels=df_r3.index, wedgeprops={
    'linewidth': 5,
    'width': 0.3,
    'edgecolor': 'white'})

plt.pie(df2_r3,radius=0.7,pctdistance=0.55, labels=df2_r3.index, wedgeprops={
    'linewidth': 5,
    'width': 0.7,
    'edgecolor': 'white'
})

plt.show()